Patch Lifecycle (Compliance)

Tracks the events emitted by the Approval engine and the events detected by the Patch Status v2 monitor and aligns this to a Patch lifecycle model.

Methodology: Kimball Star Schema.

Fact Type: Accumulating Snapshot

Grain: A Patch Guid on a Device. There is only 1 row per Device/Patch Guid combo

The major phases of the lifecycle as:

  • No Approval: A patch has been detected by the Operating System as required and the Patch Server notified. The MSP has yet to take any approval actions.
  • Not Required: The MSP has made the positive decision that a patch should be Declined or Not Approved.
  • Pending: A patch that has been Approved but has not yet been Installed i.e no installation event has yet been detected.
  • Installed: A patch has been installed on a Device. Specifically this means that the first installed detection event has occurred.
  • Removed: A patch that was previously installed is no longer on the Device. For Patch Compliance reporting, Removed patches are treated as Not Included and are excluded from compliance score calculations.

Patch Lifecycle (Compliance) schema

A diagram detailing trhe dimensions of Facct_Patch_Lifecycle

Patch Lifecycle (Compliance) table joins

FACT: FACT_PATCH_LIFECYCLE

Dimension Dimension key Fact key Cardinality

DIM_TENANT

TENANT_DK

TENANT_DK

1:N

DIM_CUSTOMER_LATEST

CUSTOMER_DK

CUSTOMER_DK

1:N

DIM_SITE_LATEST

SITE_DK

SITE_DK

1:N

DIM_DEVICE_SUMMARY_LATEST

DEVICE_DK

DEVICE_DK

1:N

DIM_DATE_APPROVAL *

CALENDAR_DATE

APPROVAL_DATE_EARLIEST

1:N

DIM_DATE_INSTALLED *

CALENDAR_DATE

INSTALLED_EARLIEST

1:N

DIM_PATCH

PATCH_DK

PATCH_DK

1:N

* DIM_DATE_APPROVAL and DIM_DATE_INSTALLED are example alias tables of DIM_DATE.

DIM PATCH GLOBAL table join

DIM: DIM_PATCH

Dimension Dimension key Dimension key Cardinality
DIM_PATCH_GLOBAL PATCH_GUID PATCH_GUID 1:N

Boilerplate for Patch Lifecycle (Compliance) joins 

with DIM_DATE_APPROVAL as
(Select * from SHARED_ANALYTICS_DATA.SHARED_DATA.DIM_DATE),
DIM_DATE_INSTALLED as 
(select * from SHARED_ANALYTICS_DATA.SHARED_DATA.DIM_DATE)
Select
-- enter columns here
From SHARED_ANALYTICS_DATA.SHARED_DATA.FACT_PATCH_LIFECYCLE f
Join SHARED_ANALYTICS_DATA.SHARED_DATA.DIM_CUSTOMER_LATEST cl on f.Customer_DK = cl.Customer_DK
join SHARED_ANALYTICS_DATA.SHARED_DATA.DIM_SITE_LATEST sl on f.Site_DK = sl.Site_DK
join SHARED_ANALYTICS_DATA.SHARED_DATA.DIM_DEVICE_SUMMARY_LATEST dl on f.Device_DK = dl.Device_DK
join SHARED_ANALYTICS_DATA.SHARED_DATA.DIM_DATE_APPROVAL da on f.Approval_Date_Earliest = da.Calendar_Date
join SHARED_ANALYTICS_DATA.SHARED_DATA.DIM_DATE_INSTALLED di on f.Installed_Earliest = di.Calendar_Date
join SHARED_ANALYTICS_DATA.SHARED_DATA.DIM_PATCH p on f.Patch_DK = p.Patch_DK
join SHARED_ANALYTICS_DATA.SHARED_DATA.DIM_PATCH_GLOBAL pg on p.Patch_GUID = pg.Patch_GUID

-- Group By columns here
-- Having clauses here
-- Order by columns here